 /**
	
 */

Alter PROC SP_CREATE_UPDATE_OSEP_FG_LOT (
 
 @PART_NUMBER 		VARCHAR(50), 
 @LOT_NUMBER 		VARCHAR(50), 
 @QTY_IN 		INT,
 @VENDOR_NAME 		VARCHAR(50),
 @STATUS 		VARCHAR(50),
 @INVENTORY_TYPE        VARCHAR(50),
 @ASSMBLY_VENDOR	VARCHAR(5),
 @DATE_IN		DATETIME
 
 )
 
 AS
 BEGIN
 
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @THIS_LOT_STATUS VARCHAR(50)
	--Declare @DATE_IN datetime	
 
 	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT, PROCESS_STEP WHERE PS_TYPE_NAME LIKE @INVENTORY_TYPE AND PRODUCT.PART_NUMBER LIKE @PART_NUMBER AND PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID 
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @INVENTORY_TYPE AND L_VENDOR_NAME = @VENDOR_NAME
	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
 	BEGIN
 		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER LIKE @PART_NUMBER AND PS_TYPE_NAME LIKE @INVENTORY_TYPE AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
		IF @PROCESS_STEP_ID IS NULL
 		BEGIN
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @inventory_type AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, @inventory_type, 'Discrepancy Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )
			END			
 			-- DUMMY UPDATE TO AVOID SQLEXCEPTION
 			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
 			RETURN
 		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
		END
 	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
	END 	
 	
 	SELECT @THIS_LOT_STATUS = LOT_CURRENT_STATUS, @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER

	/*SELECT TOP 1 @DATE_IN = LT.DATE_IN FROM LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS, LOCATION L
	WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND LT.LOCATION_ID = L.LOCATION_ID AND PS.PS_TYPE_NAME = @INVENTORY_TYPE
	AND L.L_VENDOR_NAME <> @VENDOR_NAME AND LT.LOT_NUMBER = @LOT_NUMBER*/

	/*if date in is empty to set date in as current date*/
	/*IF @DATE_IN IS NULL OR @DATE_IN = ''
	BEGIN
		SET @DATE_IN = CURRENT_TIMESTAMP
	END*/	

 	IF @LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, Lot_Drop_Out ) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @DATE_IN, @STATUS, CURRENT_TIMESTAMP, 0, 'PROD', 0 )
		SELECT @LOT_ID = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
				VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, @inventory_type,'New Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )
		
		INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_DATE_IN, TIMESTAMP, ASSEMBLY_VENDOR, IL_QTY_BALANCE ) VALUES (@LOT_ID, @QTY_IN, @DATE_IN, CURRENT_TIMESTAMP, @ASSMBLY_VENDOR, @QTY_IN  )		
		
		IF @ASSMBLY_VENDOR IS NULL
		BEGIN

			--FOR Assembly Location Vendor ALERT
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND NOTIFICATION_NAME = 'Assembly Location Vendor Notification' AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO NOTIFICATION_ALERT_TABLE ( PART_NUMBER, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_NAME, PROCESS_TYPE, NOTIFICATION_NAME, WAFER_QTY, DIE_QTY, WAFER_ID, STATUS,TIMESTAMP )
					VALUES ( @PART_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @VENDOR_NAME, @INVENTORY_TYPE, 'Assembly Location Vendor Notification', NULL, @QTY_IN, NULL, 'Open', CURRENT_TIMESTAMP )
			END

		END
		
				
	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @STATUS, DATE_IN = @DATE_IN, LOT_CATEGORY = 'PROD', LOT_ENTERED_BY = 0, Lot_Drop_Out = 0, Qty_in = @QTY_IN WHERE LOT_ID = @LOT_ID

		UPDATE INVENTORY_LOT SET TIMESTAMP = CURRENT_TIMESTAMP, IL_DATE_IN = @DATE_IN, IL_QTY_IN = @QTY_IN, IL_QTY_BALANCE = @QTY_IN WHERE LOT_ID = @LOT_ID

		IF @ASSMBLY_VENDOR IS NULL
		BEGIN

			--FOR Assembly Location Vendor ALERT
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND NOTIFICATION_NAME = 'Assembly Location Vendor Notification' AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO NOTIFICATION_ALERT_TABLE ( PART_NUMBER, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_NAME, PROCESS_TYPE, NOTIFICATION_NAME, WAFER_QTY, DIE_QTY, WAFER_ID, STATUS,TIMESTAMP )
					VALUES ( @PART_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @VENDOR_NAME, @INVENTORY_TYPE, 'Assembly Location Vendor Notification', NULL, @QTY_IN, NULL, 'Open', CURRENT_TIMESTAMP )
			END

		END
	END

	/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
	EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @DATE_IN
	
 END